with net_message as (
    select id
         , code
         , name
         , agent_code
         , agent_name
         , provider_id
         , provider_desc
         , city_id
         , city_desc
         , zone_code
         , zone_name
         , zone_id
         , area_code
         , area_name
         , leaf_area_id
         , network_type
    from jms_dim.dim_network_whole_massage
),
     order_mark as (
         select waybill_id
              , field_name
              , field_value
         from (
                  select waybill_id
                       , field_name
                       , field_value
                       , row_number() over (partition by waybill_id order by update_time desc ) as rn
                  from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt
                  where dt <= '{{ execution_date | cst_ds }}'
                    and dt >= date_add('{{ execution_date | cst_ds }}', -59)
                    and field_name in ('JYsecond','JYthird')
              ) a
         where rn = 1
     ),
     wide_table as (
         select waybill_no
              , max(taking_later_scantime)   as taking_later_scantime
              , max(instore_later_scantime)  as instore_later_scantime
              , max(outstore_later_scantime) as outstore_later_scantime
              , max(deliver_later_scantime)  as deliver_later_scantime
              , max(sign_later_scantime)     as sign_later_scantime
              , max(customer_code)           as customer_code
              , max(customer_name)           as customer_name
         from jms_dwd.dwd_wide_unsign_summary_waybill_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -59)
         group by waybill_no
     ),
     reback_transfer as (
         select waybill_no
         from jms_dwd.dwd_tab_reback_transfer_express_base_dt
         where dt <= '{{ execution_date | cst_ds }}'
           and dt >= date_add('{{ execution_date | cst_ds }}', -59)
           and apply_type_code <> 5 --申请类型4:退回  5：转寄
           and status not in (3, 4) --状态 1待审核，2已审核,3 取消申请,4.驳回
         group by waybill_no
     ),
     new_net as (
         select network_code    as agent_code
              , network_name    as agent_name
              , bs_network_code as bs_agent_code
              , bs_network_name as bs_agent_name
         from jms_dim.dim_sqs_region_agent_config_base
         where report_type = 3
         group by network_code
                , network_name
                , bs_network_code
                , bs_network_name
     ),
     three_code as (
         select staff_code,
                tail_code,
                tail_name
         from (
                  select staff_code,
                         tail_code,
                         tail_name,
                         row_number() over (partition by staff_code order by tail_update_time desc ) as rn
                  from jms_dim.dim_cn_three_codes_dt
                  where dt = '{{ execution_date | cst_ds_nodash }}'
              ) t
         where rn = 1
     ),
     order_interceptor_network as (
         select network_code --异常网点code
              , network_name --异常网点name
              , effect_time_start
              , effect_time_end
         from jms_dm.dm_customer_complaint_order_interceptor_dt
         where dt = '{{ execution_date | cst_ds }}'
           and type = 1 --异常类型 1异常区域 ,异常网点 3异常三段码
         group by network_code --异常网点code
                , network_name --异常网点name
                , effect_time_start
                , effect_time_end
     ),
     order_interceptor_third as (
         select network_code   --异常网点code
              , network_name   --异常网点name
              , the_third_code --异常三段码
              , effect_time_start
              , effect_time_end
         from jms_dm.dm_customer_complaint_order_interceptor_dt
         where dt = '{{ execution_date | cst_ds }}'
           and type = 3 --异常类型 1异常区域 2异常网点 3异常三段码
         group by network_code --异常网点code
                , network_name --异常网点name
                , effect_time_start
                , effect_time_end
                , the_third_code --异常三段码
     ),
     deletes as (
         select waybill_no
              , case t.work_type
                    when 1 then '普通工单'
                    when 2 then '平台工单'
                    when 3 then '理赔工单'
                    when 4 then '邮政工单'
                    else '其他' end                                    as work_type
              , concat('{{ execution_date | cst_ds }}', ' 12:00:00') as create_time
         from jms_dwd.dwd_sqs_complaint_report_culling_record_base t
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= date_add('{{ execution_date | cst_ds }}', 1)
           and substr(create_time, 1, 19) >
               concat(concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01'), ' 12:00:00')
           and culling_type = 1
         group by waybill_no, t.work_type
     ),
     work as ( --授理
         select waybill_no                                                                               -- string comment '运单号',
              , '普通工单'                                                          as work_type
              , first_type_code                                                                          -- string comment '一级类型编码',
              , first_type_name                                                                          -- string comment '一级类型名称',
              , second_type_code                                                                         -- string comment '二级类型编码',
              , second_type_name                                                                         -- string comment '二级类型名称',
              , cast(date_format(create_time, 'yyyy-MM-dd HH:mm:ss') as string) as create_time           -- 登记时间
              , null                                                            as trans_offices         -- string comment '转办局',
              , send_type                                                       as deliver_type          -- string comment '投递类型',
              , cast(date_format(accept_time, 'yyyy-MM-dd HH:mm:ss') as string) as accept_time-- 授理时间
              , accept_network_code                                                                      -- string comment '受理网点编码',
              , n11.name                                                        as accept_network_name   -- string comment '受理网点名称',
              , n11.network_type                                                as accept_network_type
              , n11.agent_code                                                  as accept_proxy_code     -- string comment '归属网点编码',
              , n11.agent_name                                                  as accept_proxy_name     -- string comment '归属网点名称',
              , transfer_network_code                                                                    -- string comment '转单责任网点编码',
              , n12.name                                                        as transfer_network_name -- string comment '转单责任网点名称',
              , transfer_network_type_id                                        as transfer_network_types
              , n12.network_type                                                as transfer_network_type
              , transfer_network_code                                           as duty_network_code
              , n12.name                                                        as duty_network_name
              , n12.network_type                                                as duty_network_type
              , n12.agent_code                                                  as duty_proxy_code
              , n12.agent_name                                                  as duty_proxy_name
              , case
                    when transfer_network_type_id = 335 then '中心'
                    when transfer_network_type_id <> 335 and accept_network_logo = 1 then '网点'
                    when transfer_network_type_id <> 335 and accept_network_logo = 3 then '集散'
                    else '其他' end                                               as work_order_reason_step
              , date_format(create_time, 'yyyy-MM-dd')                          as dt
              -- 新增字段
              , work_order_no
              , delivery_code
              , delivery_name
              , problem_description                                             as problem_desc
              , null                                                            as work_order_channel
              , null                                                            as express_cabinet
              , null                                                            as incoming_time
              , null                                                            as trans_time
              , null                                                               work_order_source_id  --工单创建来源 0：平台 1：消费者 2：商家',
              , null                                                               remarks               --投诉内容“未收到货”或"反馈没收到货"
              , order_source_code                                                                        --工单来源编码
              , order_source_name                                                                        --工单来源名称
              , case when n12.network_type = 4 then '转运中心' else '网点' end        as duty_type             --责任类型
              , record_group                                                                             --录单组别 1:电话组 2:在线组
              , null                                                            as send_back_code        --退回标识 1 是 2 否
              , send_type_address                                                                        --投递类型地址
              , null                                                            as ticket_first_type     --工单一级类型id
              , null                                                            as ticket_second_type    --工单二级类型id
              , create_time                                                     as dj_create_time
              , customer_type                                                   as customer_type
              , null                                                            as close_time
              , null                                                            as last_reply
         from jms_dwd.dwd_work_order_new_base_dt order1
                  left join net_message n11 on accept_network_code = n11.code
                  left join net_message n12 on transfer_network_code = n12.code
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= '{{ execution_date | cst_ds }}' --分区是create_time
           and first_type_code = 21                  --取一级问题类型为“投诉"
           and registration_network_type_id = 22     --登记网点为“总部”
           and waybill_no is not null
           and waybill_no <> ''
           and substr(waybill_no, 1, 2) <> '55'
           and create_by_name not in ('总部热线客服CN', '总部热线客服KX', '总部热线客服YG')
           and nvl(n12.agent_code, '') <> '3021888'
     ),
     project as ( --  责任
         select waybill_no                                                                                            --  string COMMENT '运单号',
              , '平台工单'                                                                       as work_type
              , first_level_code                                                                                      -- string comment '一级类型编码',
              , first_level_name                                                                                      -- string comment '一级类型名称',
              , cast(first_problem_type as string)                                           as second_level_code     -- string comment '二级类型编码',
              , second_level_name                                                                                     -- string comment '二级类型名称',
              , date_format(project.pdd_created_at, 'yyyy-MM-dd HH:mm:ss')                   as create_time           -- 登记时间
              , null                                                                         as trans_offices         -- string comment '转办局',
              , partner_name                                                                 as deliver_type          -- string comment '投递类型',
              , null                                                                         as accept_time           -- 授理时间
              , n21.code                                                                     as accept_network_code   -- bigint comment '受理网点id',
              , n21.name                                                                     as accept_network_name   -- string comment '受理网点名称',
              , n21.network_type                                                             as accept_network_type
              , n21.agent_code                                                               as accept_proxy_code     -- bigint comment '受理网点代理区id',
              , n21.agent_name                                                               as accept_proxy_name     -- string comment '受理网点代理区名称',
              , null                                                                         as transfer_network_code -- string comment '转单责任网点编码',
              , null                                                                         as transfer_network_name -- string comment '转单责任网点名称',
              , null                                                                         as transfer_network_types
              , null                                                                         as transfer_network_type
              , n22.code                                                                     as duty_network_code     -- bigint COMMENT '责任网点',
              , n22.name                                                                     as duty_network_name     -- string COMMENT '责任网点名称',
              , n22.network_type                                                             as duty_network_type
              , n22.agent_code                                                               as duty_proxy_code       -- bigint COMMENT '责任网点代理区id',
              , n22.agent_name                                                               as duty_proxy_name       -- string COMMENT '责任网点代理区名称',
              , null                                                                         as work_order_reason_step
              , date_format(project.pdd_created_at, 'yyyy-MM-dd')                            as dt
              -- 新增字段
              , work_order_no
              , staff_code                                                                   as delivery_code
              , staff_name                                                                   as delivery_name
              , problem_desc
              , work_order_channel
              , express_cabinet
              , incoming_time
              , null                                                                         as trans_time
              , work_order_source_id                                                                                  --工单创建来源 0：平台 1：消费者 2：商家',
              , remarks                                                                                               --投诉内容“未收到货”或"反馈没收到货"
              , order_source_code                                                                                     --工单来源编码
              , order_source_name                                                                                     --工单来源名称
              , case when partner_name is null or partner_name = '' then '网点' else '第三方' end as duty_type             --责任类型
              , null                                                                         as record_group          --录单组别 1:电话组 2:在线组
              , send_back_code                                                                                        --退回标识 1 是 2 否
              , express_cabinet                                                                                       --投递类型地址
              , ticket_first_type                                                                                     --工单一级类型id
              , ticket_second_type                                                                                    --工单二级类型id
              , create_time                                                                  as dj_create_time
              , null                                                                         as customer_type
              , last_close_time                                                              as close_time
              , last_reply                                                                   as last_reply
         FROM jms_dwd.dwd_project_work_order_new_base_dt project-- 平台工单
                  left join net_message n21
                            on project.accept_network_id = n21.id
                  left join net_message n22 on project.responsibility_network_id = n22.id
         WHERE project.dt >=
               add_months(concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01'),
                          -1)
           and dt <= '{{ execution_date | cst_ds }}' --分区是pdd_created_at
           and substr(pdd_created_at, 1, 10) >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and substr(pdd_created_at, 1, 10) <= '{{ execution_date | cst_ds }}'
           and waybill_no is not null
           and waybill_no <> ''
           and work_order_source_id = 1              -- 消费者 工单创建来源 0：平台 1：消费者 2：商家',
           and work_order_channel in (1, 3, 2)       --工单渠道 渠道来源(1.PDD，2.紫金山)  （remarks）：“未收到货”或"反馈没收到货"
           and substr(project.waybill_no, 1, 2) <> '55'
           and (ticket_first_type = '10000' or ticket_first_type is null)
     ),
     claim as (
         select waybill_no                                                                               -- string COMMENT '运单号',
              , '理赔工单'                                                          as work_type
              , first_type_code                                                                          -- string COMMENT '一级类型编码',
              , first_type_name                                                                          -- string COMMENT '一级类型名称',
              , second_type_code                                                                         -- string COMMENT '二级类型编码',
              , second_type_name                                                                         -- string COMMENT '二级类型名称',
              , cast(date_format(update_time, 'yyyy-MM-dd HH:mm:ss') as string) as create_time           -- 登记时间
              , null                                                            as trans_offices         -- string comment '转办局',
              , send_type                                                       as deliver_type          -- string comment '投递类型',
              , null                                                            as accept_time
              , accept_network_code                                                                      --  string COMMENT '受理网点编码',
              , n31.name                                                        as accept_network_name   --  string COMMENT '受理网点名称',
              , n31.network_type                                                as accept_network_type
              , n31.agent_code                                                  as accept_proxy_code     --       string comment '归属网点编码',
              , n31.agent_name                                                  as accept_proxy_name     --       string comment '归属网点名称',
              , null                                                            as transfer_network_code -- string comment '转单责任网点编码',
              , null                                                            as transfer_network_name -- string comment '转单责任网点名称',
              , null                                                            as transfer_network_types
              , null                                                            as transfer_network_type
              , first_duty_network_code                                         as duty_network_code     -- string COMMENT '责任网点1-网点编码',
              , n32.name                                                        as duty_network_name     -- string COMMENT '责任网点1-网点名称',
              , n32.network_type                                                as duty_network_type
              , n32.agent_code                                                  as duty_proxy_code       -- string COMMENT '责任网点1所属代理区-网点编码',
              , n32.agent_name                                                  as duty_proxy_name       -- string COMMENT '责任网点1所属代理区-网点名称',
              , case
                    when first_duty_network_type_id = 334 then '代理区'
                    when first_duty_network_type_id = 335 then '中心'
                    when first_duty_network_type_id = 336 then '网点'
                    else '其他' end                                               as work_order_reason_step
              , date_format(update_time, 'yyyy-MM-dd')                          as dt
              -- 新增字段
              , work_order_no
              , delivery_code
              , delivery_name
              , problem_description                                             as problem_desc
              , null                                                            as work_order_channel
              , null                                                            as express_cabinet
              , null                                                            as incoming_time
              , null                                                            as trans_time
              , null                                                               work_order_source_id  --工单创建来源 0：平台 1：消费者 2：商家'
              , null                                                               remarks               --投诉内容“未收到货”或"反馈没收到货"
              , order_source_code                                                                        --工单来源编码
              , order_source_name                                                                        --工单来源名称
              , case when n32.network_type = 4 then '转运中心' else '网点' end        as duty_type             --责任类型
              , record_group                                                                             --录单组别 1:电话组 2:在线组
              , null                                                            as send_back_code        --退回标识 1 是 2 否
              , send_type_address                                               as send_type_address     --退回标识 1 是 2 否
              , null                                                            as ticket_first_type     --工单一级类型id
              , null                                                            as ticket_second_type    --工单二级类型id
              , create_time                                                     as dj_create_time
              , null                                                            as customer_type
              , null                                                            as close_time
              , null                                                            as last_reply

         from jms_dwd.dwd_claim_work_order_new_base_dt claim
                  left join net_message n31 on claim.accept_network_code = n31.code
                  left join net_message n32 on claim.first_duty_network_code = n32.code
         where dt >= add_months(concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01'), -1)
           and dt <= '{{ execution_date | cst_ds }}'
           and substr(update_time, 1, 10) >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and substr(update_time, 1, 10) <= '{{ execution_date | cst_ds }}'
           and registration_network_type_id = 22 --登记网点为“总部”
           and claim_status = 6                  --'理赔工单状态 1:待总部分配 2:待总部定责 3:待网点分配 4:网点处理中 5:总部处理中 6:已关闭'
           and waybill_no is not null
           and waybill_no <> ''
     ),
     appeal as (
         select waybill_no                                                                                   --  string COMMENT '运单号',
              , '邮政工单'                                                               as work_type
              , null                                                                 as first_type_code      -- string COMMENT '一级类型编码',
              , null                                                                 as first_type_name      -- string COMMENT '一级类型名称',
              , null                                                                 as second_type_code     -- string COMMENT '二级类型编码',
              , case
                    when cause = 1 then '快件延误'
                    when cause = 2 then '快件丢失短少'
                    when cause = 3 then '快件损毁'
                    when cause = 4 then '收寄服务'
                    when cause = 5 then '收寄服务'
                    when cause = 6 then '售后服务'
                    when cause = 7 then '违规收费'
                    when cause = 9 then '代收货款'
                    else '其他' end                                                    as second_type_name     -- string COMMENT '二级类型名称',
              , cast(date_format(create_time, 'yyyy-MM-dd HH:mm:ss') as string)      as create_time
              , concat(acceptance_office_province_desc, acceptance_office_city_desc) as trans_offices
              , null                                                                 as deliver_type
              , cast(date_format(accept_time, 'yyyy-MM-dd HH:mm:ss') as string)      as accept_time
              , accept_network_code
              , n41.name                                                             as accept_network_name
              , n41.network_type                                                     as accept_network_type
              , n41.agent_code                                                       as accept_proxy_code
              , n41.agent_name                                                       as accept_proxy_name
              , null                                                                 as transfer_network_code
              , null                                                                 as transfer_network_name
              , null                                                                 as transfer_network_types
              , null                                                                 as transfer_network_type
              , blame_network_code                                                   as duty_network_code
              , n42.name                                                             as duty_network_name
              , n42.network_type                                                     as duty_network_type
              , n42.agent_code                                                       as duty_proxy_code
              , n42.agent_name                                                       as duty_proxy_name
              , null                                                                 as work_order_reason_step
              , date_format(create_time, 'yyyy-MM-dd')                               as dt
              -- 新增字段
              , appeal_no                                                            as work_order_no
              , delivery_code
              , delivery_name
              , null                                                                 as problem_desc
              , null                                                                 as work_order_channel
              , null                                                                 as express_cabinet
              , null                                                                 as incoming_time
              , trans_time
              , null                                                                    work_order_source_id --工单创建来源 0：平台 1：消费者 2：商家',
              , null                                                                    remarks              --投诉内容“未收到货”或"反馈没收到货"
              , order_source_code                                                                            --工单来源编码
              , order_source_name                                                                            --工单来源名称
              , '网点'                                                                 as duty_type            --责任类型
              , null                                                                 as record_group         --录单组别 1:电话组 2:在线组
              , null                                                                 as send_back_code       --退回标识 1 是 2 否
              , null                                                                 as send_type_address    --投递类型地址
              , null                                                                 as ticket_first_type    --工单一级类型id
              , null                                                                 as ticket_second_type   --工单二级类型id
              , create_time                                                          as dj_create_time
              , null                                                                 as customer_type
              , null                                                                 as close_time
              , null                                                                 as last_reply
         from jms_dwd.dwd_appeal_info_new_base_dt appeal
                  left join net_message n41 on appeal.accept_network_code = n41.code
                  left join net_message n42 on appeal.blame_network_code = n42.code
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= '{{ execution_date | cst_ds }}' --分区是create_time
           and waybill_no is not null
           and waybill_no <> ''
     ),
     -- 非邮政工单取创建时间最大的运单
     other_cur as (
         select *
              , row_number() over (partition by waybill_no order by create_time) as ranks
         from (
                  select *
                  from work
                  union
                  select *
                  from project
                  union
                  select *
                  from claim
              ) t1
     ),
     appeal_cur as ( -- 邮政工单去创建时间最小的运单
         select *
              , row_number() over (partition by waybill_no order by create_time ) as ranks
         from appeal
     ),
     curr as ( -- 去重规则：T-1-四种工单中运单即存在由邮政工单中，也存在其他工单中，则只保留邮政的，如果运单只在非邮政工单中，安createtime排序取最新的
         select *
         from other_cur
         where ranks = 1
           and waybill_no not in (select waybill_no from appeal_cur)
         union
         select *
         from appeal_cur
         where ranks = 1
     ),
     result_table as (
         select curr.waybill_no
              , curr.work_type
              , first_type_code
              , first_type_name
              , second_type_code
              , second_type_name
              , curr.create_time
              , trans_offices
              , deliver_type
              , accept_time
              , accept_network_code
              , accept_network_name
              , accept_network_type
              , if(net1.bs_agent_code is not null, net1.agent_code, accept_proxy_code) as accept_proxy_code
              , if(net1.bs_agent_code is not null, net1.agent_name, accept_proxy_name) as accept_proxy_name
              , transfer_network_code
              , transfer_network_name
              , transfer_network_types
              , transfer_network_type
              , duty_network_code
              , duty_network_name
              , duty_network_type
              , if(net2.bs_agent_code is not null, net2.agent_code, duty_proxy_code)   as duty_proxy_code
              , if(net2.bs_agent_code is not null, net2.agent_name, duty_proxy_name)   as duty_proxy_name
              , work_order_reason_step
              , case
                    when curr.waybill_no = deletes.waybill_no then 1
                    else 2 end                                                         as is_delete  --1删除,2未删除
              , work_order_no
              , delivery_code
              , delivery_name
              , three_code.tail_code
              , tail_name
              , problem_desc
              , work_order_channel
              , express_cabinet
              , incoming_time
              , trans_time
              , work_order_source_id                                                                 --工单创建来源 0：平台 1：消费者 2：商家',
              , remarks                                                                              --投诉内容“未收到货”或"反馈没收到货"
              , case
                    when curr.waybill_no = deletes.waybill_no then 1
                    else 0 end                                                         as his_delete --1是手工上传数据0不是
              , substr(curr.create_time, 1, 10)                                        as create_day --关联比较时间,创建时间
              , order_source_code                                                                    --工单来源编码
              , order_source_name                                                                    --工单来源名称
              , duty_type                                                                            --责任类型
              , record_group                                                                         --录单组别 1:电话组 2:在线组
              , send_back_code                                                                       --退回标识 1 是 2 否
              , if(curr.work_type in ('普通工单', '平台工单', '理赔工单'), curr.duty_network_code,
                   curr.accept_network_code)                                           as network_code
              , send_type_address
              , ticket_first_type                                                                    --工单一级类型id
              , ticket_second_type                                                                   --工单二级类型id
              , dj_create_time
              , customer_type
              , close_time
              , last_reply
              , t5.field_name
              , t5.field_value
         from curr
                  left join deletes
                            on deletes.waybill_no = curr.waybill_no
                                and deletes.work_type = curr.work_type
                  left join new_net net1 on curr.accept_proxy_code = net1.bs_agent_code
                  left join new_net net2 on curr.duty_proxy_code = net2.bs_agent_code
                  left join three_code on nvl(curr.delivery_code, null) = three_code.staff_code
                  left join order_mark t5
                            on curr.waybill_no = t5.waybill_id
         where substr(nvl(curr.create_time, deletes.create_time), 1, 10) >=
               concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and substr(nvl(curr.create_time, deletes.create_time), 1, 10) <= '{{ execution_date | cst_ds }}'
     ),
     result_table2 as (
         select t1.waybill_no
              , t1.work_type
              , t1.first_type_code
              , t1.first_type_name
              , t1.second_type_code
              , t1.second_type_name
              , t1.create_time
              , t1.trans_offices
              , t1.deliver_type
              , t1.accept_time
              , t1.accept_network_code
              , t1.accept_network_name
              , t1.accept_network_type
              , t1.accept_proxy_code
              , t1.accept_proxy_name
              , t1.transfer_network_code
              , t1.transfer_network_name
              , t1.transfer_network_types
              , t1.transfer_network_type
              , t1.duty_network_code
              , t1.duty_network_name
              , t1.duty_network_type
              , t1.duty_proxy_code
              , t1.duty_proxy_name
              , t1.work_order_reason_step
              , case
                    when t1.is_delete = 2 and ((t2.network_code is not null or t3.the_third_code is not null) or
                                               (t1.work_type = '平台工单' and t1.work_order_channel = 1 and
                                                t1.work_order_source_id = 1 and
                                                t1.remarks in ('未收到货', '反馈没收到货'))) then 1
                    else is_delete end                                       as is_delete --1代表关联上了,说明删除了 2没关联上未删除
              , t1.work_order_no
              , t1.delivery_code
              , t1.delivery_name
              , t1.tail_code
              , t1.tail_name
              , t1.problem_desc
              , t1.work_order_channel
              , t1.express_cabinet
              , t1.incoming_time
              , t1.trans_time
              , t1.his_delete                                                             --1是手工上传数据0不是
              , t1.work_order_source_id                                                   --工单创建来源 0：平台 1：消费者 2：商家',新增字段
              , t1.remarks                                                                --投诉内容“未收到货”或"反馈没收到货",新增字段
              , t1.create_day                                                             --日期 创建时间截取
              , t2.network_code                                              as del_network_code
              , t2.network_name                                              as del_network_name
              , t1.order_source_code                                                      --工单来源编码
              , t1.order_source_name                                                      --工单来源名称
              , t1.duty_type                                                              --责任类型
              , t1.record_group                                                           --录单组别 1:电话组 2:在线组
              , t1.send_back_code                                                         --退回标识 1 是 2 否
              , t1.network_code
              , t1.send_type_address
              , t1.ticket_first_type                                                      --工单一级类型id
              , t1.ticket_second_type                                                     --工单二级类型id
              , t1.dj_create_time
              , t4.taking_later_scantime
              , t4.instore_later_scantime
              , t4.outstore_later_scantime
              , t4.deliver_later_scantime
              , t4.sign_later_scantime
              , greatest(t4.outstore_later_scantime, t4.sign_later_scantime) as out_sign_time
              , customer_type
              , t1.close_time
              , t1.last_reply
              , t1.field_name
              , t4.customer_code
              , t4.customer_name
              , t1.field_value
         from result_table t1
                  left join order_interceptor_network t2
                            on t2.network_code = t1.network_code
                                and ((t1.create_day <= t2.effect_time_end and
                                      t1.create_day >= t2.effect_time_start)
                                    or (date_add(t1.create_day, -1) <= t2.effect_time_end and
                                        date_add(t1.create_day, -1) >= t2.effect_time_start)
                                    or (date_add(t1.create_day, -2) <= t2.effect_time_end and
                                        date_add(t1.create_day, -2) >= t2.effect_time_start))
                  left join order_interceptor_third t3
                            on t3.network_code = t1.network_code
                                and t3.the_third_code = t1.tail_code
                                and ((t1.create_day <= t3.effect_time_end and
                                      t1.create_day >= t3.effect_time_start)
                                    or (date_add(t1.create_day, -1) <= t3.effect_time_end and
                                        date_add(t1.create_day, -1) >= t3.effect_time_start)
                                    or (date_add(t1.create_day, -2) <= t3.effect_time_end and
                                        date_add(t1.create_day, -2) >= t3.effect_time_start))
                  left join wide_table t4
                            on t1.waybill_no = t4.waybill_no
     )
insert
overwrite
table
jms_dm.dm_waybill_customer_complaint_detail_new_dt
partition
(
dt
)
select t1.waybill_no
     , t1.work_type
     , t1.first_type_code
     , t1.first_type_name
     , t1.second_type_code
     , t1.second_type_name
     , t1.create_time
     , t1.trans_offices
     , t1.deliver_type
     , t1.accept_time
     , t1.accept_network_code
     , t1.accept_network_name
     , t1.accept_network_type
     , t1.accept_proxy_code
     , t1.accept_proxy_name
     , t1.transfer_network_code
     , t1.transfer_network_name
     , t1.transfer_network_types
     , t1.transfer_network_type
     , t1.duty_network_code
     , t1.duty_network_name
     , t1.duty_network_type
     , t1.duty_proxy_code
     , t1.duty_proxy_name
     , t1.work_order_reason_step
     , min(t1.is_delete)                   as is_delete
     , t1.create_day                       as data_time              --创建时间
     , t1.work_order_no
     , t1.delivery_code
     , t1.delivery_name
     , t1.tail_code
     , t1.tail_name
     , t1.problem_desc
     , t1.work_order_channel
     , t1.express_cabinet
     , t1.incoming_time
     , t1.trans_time
     , t1.his_delete                                                 --1是手工上传数据0不是
     , t1.work_order_source_id                                       --工单创建来源 0：平台 1：消费者 2：商家',新增字段
     , t1.remarks                                                    --投诉内容“未收到货”或"反馈没收到货",新增字段
     , t1.create_day                                                 --日期 创建时间截取
     , t1.del_network_code
     , t1.order_source_code                                          --工单来源编码
     , t1.order_source_name                                          --工单来源名称
     , t1.duty_type                                                  --责任类型
     , t3.provider_id
     , t3.provider_desc
     , t3.city_id
     , t3.city_desc
     , t3.zone_code                        as district_code
     , t3.zone_name                        as district_desc
     , t3.zone_id                          as district_id
     , t4.agent_code                       as transfer_proxy_code    --转单责任网点对应代理区code
     , t4.agent_name                       as transfer_proxy_name    --转单责任网点对应代理区name
     , t4.provider_id                      as transfer_provider_id   --转单责任网点对应省id
     , t4.provider_desc                    as transfer_provider_desc --转单责任网点对应省name
     , t4.city_id                          as transfer_city_id       --转单责任网点对应市id
     , t4.city_desc                        as transfer_city_desc     --转单责任网点对应市name
     , t4.zone_code                        as transfer_district_code --转单责任网点片区code
     , t4.zone_name                        as transfer_district_desc --转单责任网点片区desc
     , t4.zone_id                          as transfer_district_id   --转单责任网点片区id
     , t1.record_group                                               --录单组别 1:电话组 2:在线组
     , t1.send_back_code                                             --客诉退回标识 1 是 2 否
     , if(t5.waybill_no is not null, 1, 0) as reback_transfer_falg   --退转件中:是否退回,1是0否
     , t3.area_code                                                  --区域code
     , t3.area_name                                                  --区域name
     , t3.leaf_area_id                                               --区域id
     , t1.send_type_address
     , t1.network_code
     , t3.name                             as network_name
     , t3.network_type                     as network_type
     , t1.dj_create_time
     , t1.taking_later_scantime
     , t1.instore_later_scantime
     , t1.outstore_later_scantime
     , t1.deliver_later_scantime
     , t1.sign_later_scantime
     , t1.out_sign_time
     , case
           when taking_later_scantime is null and instore_later_scantime is null and outstore_later_scantime is null and
                deliver_later_scantime is null and sign_later_scantime is null
               then 1
           when dj_create_time > deliver_later_scantime and
                (dj_create_time < instore_later_scantime or dj_create_time < sign_later_scantime or
                 (instore_later_scantime is null and sign_later_scantime is null))
               then 2
           when dj_create_time >= instore_later_scantime and
                (dj_create_time < outstore_later_scantime or outstore_later_scantime is null)
               then 3
           when dj_create_time >= sign_later_scantime
               then 4
           when dj_create_time >= outstore_later_scantime
               then 5
           else 6
    end                                    as problem_link
     , t1.ticket_first_type                                          --工单一级类型id
     , t1.ticket_second_type                                         --工单二级类型id
     , t1.customer_type
     , t1.close_time
     , t1.last_reply
     , t1.field_name
     , t1.customer_code
     , t1.customer_name
     , t1.field_value
     , t1.create_day                       as dt
from result_table2 t1
         left join net_message t3
                   on t1.network_code = t3.code
         left join net_message t4
                   on t1.transfer_network_code = t4.code
         left join reback_transfer t5
                   on t1.waybill_no = t5.waybill_no
group by t1.waybill_no
       , t1.work_type
       , t1.first_type_code
       , t1.first_type_name
       , t1.second_type_code
       , t1.second_type_name
       , t1.create_time
       , trans_offices
       , t1.deliver_type
       , t1.accept_time
       , t1.accept_network_code
       , t1.accept_network_name
       , t1.accept_network_type
       , t1.accept_proxy_code
       , t1.accept_proxy_name
       , t1.transfer_network_code
       , t1.transfer_network_name
       , t1.transfer_network_types
       , t1.transfer_network_type
       , t1.duty_network_code
       , t1.duty_network_name
       , t1.duty_network_type
       , t1.duty_proxy_code
       , t1.duty_proxy_name
       , t1.work_order_reason_step
       , t1.work_order_no
       , t1.delivery_code
       , t1.delivery_name
       , t1.tail_code
       , t1.tail_name
       , t1.problem_desc
       , t1.work_order_channel
       , t1.express_cabinet
       , t1.incoming_time
       , t1.trans_time
       , t1.his_delete
       , t1.work_order_source_id --工单创建来源 0：平台 1：消费者 2：商家',新增字段
       , t1.remarks              --投诉内容“未收到货”或"反馈没收到货",新增字段
       , t1.create_day           --日期 创建时间截取
       , t1.del_network_code
       , t1.order_source_code    --工单来源编码
       , t1.order_source_name    --工单来源名称
       , t1.duty_type            --责任类型
       , t3.provider_id
       , t3.provider_desc
       , t3.city_id
       , t3.city_desc
       , t3.zone_code
       , t3.zone_name
       , t3.zone_id
       , t4.agent_code
       , t4.agent_name
       , t4.provider_id
       , t4.provider_desc
       , t4.city_id
       , t4.city_desc
       , t4.zone_code
       , t4.zone_name
       , t4.zone_id
       , t1.record_group         --录单组别 1:电话组 2:在线组
       , t1.send_back_code       --退回标识 1 是 2 否
       , t3.area_code
       , t3.area_name
       , t3.leaf_area_id
       , if(t5.waybill_no is not null, 1, 0)
       , t1.network_code
       , t3.name
       , t1.send_type_address
       , t3.network_type
       , t1.dj_create_time
       , t1.taking_later_scantime
       , t1.instore_later_scantime
       , t1.outstore_later_scantime
       , t1.deliver_later_scantime
       , t1.sign_later_scantime
       , t1.out_sign_time
       , t1.ticket_first_type    --工单一级类型id
       , t1.ticket_second_type   --工单二级类型id
       , t1.customer_type
       , t1.close_time
       , t1.last_reply
       , t1.field_name
       , t1.customer_code
       , t1.customer_name
       , t1.field_value
    distribute by dt
;
